/**
 * SellDAO.java
 *
 * $Id$
 *
 */
package br.com.devmedia.completeswingapp.dao;

import br.com.devmedia.completeswingapp.entity.Sell;
import br.com.devmedia.completeswingapp.entity.SellItem;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

/**
 * 
 * @author Dyego Souza do Carmo
 * @since 
 */
public class SellDAO extends GenericDAO {

    private static final long serialVersionUID = 1L;

    public SellDAO() {
    }

    public int addSell(Sell sl, boolean cascade) throws SQLException {
        sl.setId(getNextId("SELLS"));
        String query = "INSERT INTO APP.SELLS (ID,ID_VENDOR,DATEOFSALE,TOTAL) values (?,?,?,?)";
        // Sum the Total of Sell
        float totalOfSale = 0f;
        for (SellItem item : sl.getItens()) {
            totalOfSale += item.getProduct().getPrice() * item.getQnt();
        }
        // Create the Sell in DATABASE
        executeCommand(query, sl.getId(), sl.getVendor().getId(), sl.getDataOfSale(), totalOfSale);
        if (cascade) {
            // Persist the Itens
            for (SellItem item : sl.getItens()) {
                addSellItem(item);
            }
        }
        return sl.getId();
    }

    public int addSellItem(SellItem sli) throws SQLException {
        sli.setId(getNextId("SELLSITEM"));
        String query = "INSERT INTO APP.SELLSITEM (ID,ID_PRODUCT,QNT,ID_SELL) values (?,?,?,?)";
        executeCommand(query, sli.getId(), sli.getProduct().getId(), sli.getQnt(), sli.getSell().getId());
        return sli.getId();
    }

    public void updateSell(Sell sl, boolean cascade) throws SQLException {
        String query = "UPDATE APP.SELLS SET ID_VENDOR=?,TOTAL=? WHERE ID = ?";
        // Sum the Total of Sell
        float totalOfSale = 0f;
        for (SellItem item : sl.getItens()) {
            totalOfSale += item.getProduct().getPrice() * item.getQnt();
        }
        executeCommand(query, sl.getVendor().getId(), totalOfSale, sl.getId());
        if (cascade) {
            for (SellItem item : sl.getItens()) {
                updateSellItem(item);
            }
        }
    }

    public void updateSellItem(SellItem sli) throws SQLException {
        String query = "UPDATE APP.SELLSITEM SET ID_PRODUCT = ?,QNT=? WHERE ID = ?";
        executeCommand(query, sli.getProduct().getId(), sli.getQnt(), sli.getId());
    }

    public void removeSell(int idSell) throws SQLException {
        executeCommand("DELETE FROM APP.SELLSITEM where ID_SELL = ?", idSell);
        executeCommand("DELETE FROM APP.SELLS WHERE ID = ?", idSell);
    }

    public void removeSellItem(int idSellItem) throws SQLException {
        executeCommand("DELETE FROM APP.SELLSITEM where ID = ?", idSellItem);
    }

    public Sell getSell(int idSell) throws SQLException {
        ResultSet rs = executeQuery("SELECT * FROM APP.SELLS WHERE ID = ?", idSell);
        Sell sl = null;
        if (rs.next()) {
            sl = populateSell(rs, true);
        }
        rs.close();

        return sl;
    }

    public List<Sell> getAllSells() throws SQLException {
        ResultSet rs = executeQuery("SELECT * FROM APP.SELLS");
        List<Sell> toReturn = new LinkedList<Sell>();
        while (rs.next()) {
            toReturn.add(populateSell(rs, true));
        }
        rs.close();
        return toReturn;
    }
    

    public Sell populateSell(ResultSet rs, boolean populateItens) throws SQLException {
        Sell toReturn = new Sell();
        UserDAO userDAO = new UserDAO();
        toReturn.setId(rs.getInt("ID"));
        toReturn.setVendor(userDAO.getUser(rs.getInt("ID_VENDOR")));
        toReturn.setDataOfSale(rs.getDate("DATEOFSALE"));
        toReturn.setTotal(rs.getFloat("TOTAL"));
        if (populateItens) {
            toReturn.setItens(getSellItens(toReturn));
        }
        return toReturn;
    }

    public SellItem getSellItem(int idSellItem) throws SQLException {
        ResultSet rs = executeQuery("SELECT * FROM APP.SELLSITEM where ID = ?", idSellItem);
        SellItem sli = null;
        if (rs.next()) {
            sli = populateSellItem(rs);
        }
        rs.close();
        return sli;
    }

    public List<SellItem> getSellItens(Sell sl) throws SQLException {
        List<SellItem> toReturn = new LinkedList<SellItem>();
        ResultSet rs = executeQuery("SELECT * from APP.SELLSITEM where ID_SELL = ?", sl.getId());
        while (rs.next()) {
            toReturn.add(populateSellItem(rs, sl));
        }
        rs.close();
        return toReturn;
    }

    public SellItem populateSellItem(ResultSet rs, Sell... sell) throws SQLException {
        SellItem toReturn = new SellItem();
        ProductDAO prodDAO = new ProductDAO();
        toReturn.setId(rs.getInt("ID"));
        toReturn.setQnt(rs.getInt("QNT"));
        toReturn.setProduct(prodDAO.getProduct(rs.getInt("ID_PRODUCT")));
        if (sell != null && sell.length >0 ) {
            toReturn.setSell(sell[0]);
        } else {
            toReturn.setSell(getSell(rs.getInt("ID_SELL")));
        }
        return toReturn;
    }
}
